package database;

import java.sql.*;
import org.apache.log4j.Logger;

public class Database {

    static Logger logger = Logger.getLogger(Database.class);
    
    public static void createUser(String appID, String appVersion) {
        Connection con = null;
        CallableStatement stmt = null;
        ResultSet res = null;
        try {
            con = MyConnection.getConnection();
            try {
                stmt = con.prepareCall("call create_user(?,?,?)");
                stmt.setString(1, appID);
                stmt.setString(2, appVersion);
                stmt.registerOutParameter(3, Types.VARCHAR);
                stmt.execute();
                System.out.println("User Created");
                String msg = stmt.getString(3);
                if (!msg.equalsIgnoreCase("SUCCESS")) {
                    logger.fatal("failed to create user");
                }
            } catch (Exception ex) {
                logger.fatal("DB query exception",ex);
            }

        } catch (Exception ex) {
            //connection exception
            ex.printStackTrace();
        } finally {

            MyConnection.close(con);
            MyConnection.close(stmt);
            MyConnection.close(res);

        }
    }

    public static boolean checkUser(String appID) {
        Connection con = null;
        CallableStatement stmt = null;
        ResultSet res = null;
        try {
            con = MyConnection.getConnection();
            try {
                stmt = con.prepareCall("call check_user(?,?,?)");
                stmt.setString(1, appID);
                stmt.registerOutParameter(2, Types.VARCHAR);
                stmt.registerOutParameter(3, Types.VARCHAR);
                stmt.execute();
                String msg = stmt.getString(3);
                if (!msg.equalsIgnoreCase("SUCCESS")) {
                    //ki korbi tui janosh
                } else {
                    String stat = stmt.getString(2);
                    if (stat.equalsIgnoreCase("FOUND")) {
                        return true;
                    }
                    return false;
                }
            } catch (Exception ex) {
                logger.fatal("DB query exception",ex);
            }

        } catch (Exception ex) {
            //connection exception
            ex.printStackTrace();
        } finally {

            MyConnection.close(con);
            MyConnection.close(stmt);
            MyConnection.close(res);

        }
        return false;
    }

    public static boolean checkSession(String sessionID) {
        Connection con = null;
        CallableStatement stmt = null;
        ResultSet res = null;
        try {
            con = MyConnection.getConnection();
            try {
                stmt = con.prepareCall("call check_session(?,?,?)");
                stmt.setString(1, sessionID);
                stmt.registerOutParameter(2, Types.VARCHAR);
                stmt.registerOutParameter(3, Types.VARCHAR);
                stmt.execute();
                String msg = stmt.getString(3);
                if (!msg.equalsIgnoreCase("SUCCESS")) {
                    //ki korbi tui janosh
                } else {
                    String stat = stmt.getString(2);
                    if (stat.equalsIgnoreCase("active")) {
                        return true;
                    }
                    return false;
                }
            } catch (Exception ex) {
                logger.fatal("DB query exception");
            }

        } catch (Exception ex) {
            //connection exception
            ex.printStackTrace();
        } finally {

            MyConnection.close(con);
            MyConnection.close(stmt);
            MyConnection.close(res);

        }
        return false;
    }

    public static void createSession(String sessionId, String appId, String MNC,
            String MCC, String gsmCellId, String IPClient, String IPServer) {
        Connection con = null;
        CallableStatement stmt = null;
        ResultSet res = null;
        try {
            con = MyConnection.getConnection();
            try {

                stmt = con.prepareCall("call create_session(?,?,?,?,?,?,?,?)");
                stmt.setString(1, sessionId);
                stmt.setString(2, appId);
                stmt.setString(3, MNC);
                stmt.setString(4, MCC);
                stmt.setString(5, gsmCellId);
                stmt.setString(6, IPClient);
                stmt.setString(7, IPServer);
                stmt.registerOutParameter(8, Types.VARCHAR);
                stmt.execute();
                String msg = stmt.getString(8);
                if (!msg.equalsIgnoreCase("SUCCESS")) {
                    //ki korbi tui janosh
                }
            } catch (Exception ex) {
                logger.fatal("DB query exception",ex);
            }

        } catch (Exception ex) {
            //connection exception
            ex.printStackTrace();
        } finally {

            MyConnection.close(con);
            MyConnection.close(stmt);
            MyConnection.close(res);

        }
    }


    public static void createTestResult(
            String sessionId,
            String appId,
            int testProtocoId,
            String IPServerUsedToTest,
            String IPClientUsedToTest,
            String PORTServerUsedToTest,
            String PORTClientUsedToTest,
            int dataSizeInByte,
            int TotalElapsedTimeForFileSendingInMillisecond,
            boolean isTestSuccessful,
            String testType,
            String isp,
            String country,
            String clientToServerResult,
            String serverToClientResult) {
        Connection con = null;
        CallableStatement stmt = null;
        ResultSet res = null;
        try {
            con = MyConnection.getConnection();
            try {

                stmt = con.prepareCall("call create_test_result(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)");
                stmt.setString(1, sessionId);
                stmt.setString(2, appId);
                stmt.setInt(3, testProtocoId);
                stmt.setString(4, IPServerUsedToTest);
                stmt.setString(5, IPClientUsedToTest);
                stmt.setString(6, PORTServerUsedToTest);
                stmt.setString(7, PORTClientUsedToTest);
                stmt.setInt(8, dataSizeInByte);
                stmt.setInt(9, TotalElapsedTimeForFileSendingInMillisecond);
                stmt.setBoolean(10, isTestSuccessful);
                stmt.setString(11, testType);
                stmt.setString(12,isp);
                stmt.setString(13, country);
                stmt.setString(14, clientToServerResult);
                stmt.setString(15, serverToClientResult);
                stmt.registerOutParameter(16, Types.VARCHAR);
                stmt.execute();
                String msg = stmt.getString(16);
                if (!msg.equalsIgnoreCase("SUCCESS")) {
                    //ki korbi tui janosh
                }
            } catch (Exception ex) {
                logger.fatal("DB query exception",ex);
            }

        } catch (Exception ex) {
            //connection exception
            ex.printStackTrace();
        } finally {

            MyConnection.close(con);
            MyConnection.close(stmt);
            MyConnection.close(res);

        }
    }

    public static void createEmail(String sessionID, String emailID) {
        Connection con = null;
        CallableStatement stmt = null;
        ResultSet res = null;
        try {
            con = MyConnection.getConnection();
            try {
                stmt = con.prepareCall("call create_email(?,?,?)");
                stmt.setString(1, sessionID);
                stmt.setString(2, emailID);
                stmt.registerOutParameter(3, Types.VARCHAR);
                stmt.execute();
                System.out.println("Email Created");
                String msg = stmt.getString(3);
                if (!msg.equalsIgnoreCase("SUCCESS")) {
                    //ki korbi tui janosh
                }
            } catch (Exception ex) {
                logger.fatal("DB query exception",ex);
            }

        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {

            MyConnection.close(con);
            MyConnection.close(stmt);
            MyConnection.close(res);

        }
    }
    
    public static boolean sessionInvalidate(String sessionID) {
        Connection con = null;
        PreparedStatement stmt = null;
        int res = 0;
        try {
            con = MyConnection.getConnection();
            try {
                stmt = con.prepareStatement("UPDATE  `session` SET `status`=\"inactive\" WHERE session_id = \"" + sessionID + "\";");
                res = stmt.executeUpdate();
            } catch (Exception ex) {
                logger.fatal("Can not invalidate session", ex);
                logger.fatal(stmt.toString());
                return false;
            }

        } catch (Exception ex) {
            //connection exception
            logger.fatal("Can not create database connection", ex);
            return false;
        } finally {
            MyConnection.close(con);
            MyConnection.close(stmt);
        }
        if (res > 0) {
            return true;
        } else {
            return false;
        }
    }
}
